package dao;

import bean.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import util.DataSourceUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    //根据用户名与密码查找用户
    public User findUserByUsernameAndPassword(String username, String password) throws SQLException {
        String sql="select * from user where username=? and passwd=?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new BeanHandler<User>(User.class),username,password);
    }

    // 查找所有学生信息
    public List<User> listAll() throws SQLException {
        String sql = "select * from user";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new BeanListHandler<User>(User.class));
    }

    //添加用户
    public void add(User user) throws SQLException{
        //1.定义sql
        String sql = "insert into user values(null,?,?,?,?,?,?,?,?)";
        //2.执行sql
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        runner.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(),user.getUsername(),user.getPasswd());
    }

    //根据id查找用户
    public User findById(String id) throws SQLException {
        String sql = "select * from user where id = ?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new BeanHandler<User>(User.class), id);
    }
    //修改用户数据
    public void updateuser(User user) throws SQLException {
        String sql = "update user set name = ?,gender = ? ,age = ? , address = ? , qq = ?, email = ?,username = ?,passwd = ? where id = ?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        runner.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getUsername(),user.getPasswd(),user.getId());
    }
    //根据id删除用户
    public void delete(String id) throws SQLException {
        //1.定义sql
        String sql = "delete from user where id = ?";
        //2.执行sql
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        runner.update(sql,id);
    }
    //多条件查询
    public List<User> findUserByManyCondition(String id, String name, String category, String username) throws SQLException{
        List<Object> list = new ArrayList<Object>();
        String sql = "select * from user where 1=1 ";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        if (id != null && id.trim().length() > 0) {
            sql += " and id=?";
            list.add(id);
        }
        if (name != null && name.trim().length() > 0) {
            sql += " and name=?";
            list.add(name);
        }
        if (category != null && category.trim().length() > 0) {
            sql += " and address=?";
            list.add(category);
        }
        if (username != null && username.trim().length() > 0) {
            sql += " and username=?";
            list.add(username);
        }
        Object[] params = list.toArray();
            return runner.query(sql, new BeanListHandler<User>(User.class), params);
    }
}
